/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.feriaderemates.dao.user;


import com.feriaderemates.domain.userAccount.UserFeedback;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 *
 * @author Lenovo
 */
public class UserFeedbackDao extends JdbcDaoSupport implements UserFeedbackDaoInterface {

	//Queries
	private static String GET_USER_NAME = "SELECT username FROM portal.user WHERE user_id = ?";
	private static String RECEIVER_FEEDBACK_LIST = "SELECT user_feedback.user_feedback_id,user_feedback.comments,"
                 + " user_feedback.date,user_feedback.sender_username,user_feedback.feedback_level," 
                 + " user_feedback.receiver_username,   user_feedback.listing_id,   listing.description"
                 + " FROM   portal.listing,   portal.user_feedback WHERE " 
                 + " listing.listing_id = user_feedback.listing_id AND user_feedback.receiver_username = ?" ;
	private static String SENDER_FEEDBACK_LIST ="SELECT user_feedback.user_feedback_id,user_feedback.comments,"
                 + " user_feedback.date,user_feedback.sender_username,user_feedback.feedback_level," 
                 + " user_feedback.receiver_username,   user_feedback.listing_id,   listing.description"
                 + " FROM   portal.listing,   portal.user_feedback WHERE " 
                 + " listing.listing_id = user_feedback.listing_id AND user_feedback.sender_username = ?" ;
        private static String ADD_USER_FEEDBACK = "INSERT INTO portal.user_feedback (comments,date,sender_username,feedback_level,listing_id,"
                 + "receiver_username) VALUES (?,NOW(),?,?,?,? )"; 
	private static String UPDATE_USER_FEEDBACK = "UPDATE portal.user_feedback SET comments = ?,feedback_level=? ," +
			" date = NOW() WHERE user_feedback_id = ?";
	private static String DELETE_USER_FEEDBACK = "DELETE FROM portal.user_feedback WHERE user_feedback_id = ? ";
	
	
	/**
     * Gets received user's feedback from the db
     * 
     * @param receiverUserId the user that received the feedback
     * @return the list of Feedback
     */
	
    public List<UserFeedback> getReceivedUserFeedback(int receiverUserId) {
       String  user_name = (String) getJdbcTemplate().queryForObject(GET_USER_NAME, new Object[]{receiverUserId}, String.class);
       return getJdbcTemplate().query(RECEIVER_FEEDBACK_LIST,new Object[]{user_name}, new UserFeedbackrowMapper());
    }
    
    /**
     * Gets feedback that a user sent 
     * 
     * @param senderUserId the user that sent the feedback
     * @return the list of Feedback
     */

    public List<UserFeedback> getSentUserFeedback(int senderUserId) {
    	String  user_name = (String) getJdbcTemplate().queryForObject(GET_USER_NAME, new Object[]{senderUserId}, String.class);
    	return getJdbcTemplate().query(SENDER_FEEDBACK_LIST,new Object[]{user_name}, new UserFeedbackrowMapper());
    }

    /**
     * Add feedback to a user
     * 
     * @param userFeedback the feedback to add
     * @return the new feedback identifier
     */
    public int addUserFeedback(final UserFeedback userFeedback) {
    	KeyHolder keyHolder = new GeneratedKeyHolder();
    	getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(ADD_USER_FEEDBACK,Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, userFeedback.getMessage());
                ps.setString(2, userFeedback.getSenderUsername());
                ps.setString(3, userFeedback.getFeedbackLevel().name());
                ps.setLong(4, userFeedback.getListingId());
                ps.setString(5, userFeedback.getReceiverUsername());
                return ps;
            }
        }, keyHolder);
    	Object key = keyHolder.getKeys().get("user_feedback_id");
        return (Integer) key;
    }

    /**
     * Updates a feedback for a user
     * 
     * @param userFeedback the feedback to update
     */
    public void updateUserFeedback(final UserFeedback userFeedback) {
    	getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(UPDATE_USER_FEEDBACK);
                ps.setString(1, userFeedback.getMessage());
                ps.setString(2, userFeedback.getFeedbackLevel().name());
                ps.setInt(3, userFeedback.getUserFeedbackId());
                return ps;
            }
        });
    }
    /**
     * Deletes a feedback with the given userFeedbackId;
     * 
     * @param userFeedbackId 
     */
    public void deleteUserFeedback(final int userFeedbackId) {
    	getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(DELETE_USER_FEEDBACK);
                ps.setInt(1, userFeedbackId);
                return ps;
            }
        });
    }
   
}
